package study.itheima.mystu.dao;

import study.itheima.mystu.domian.Student;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class StudentDaoImpl implements StudentDao {

    @Override
    public ArrayList<Student> findAll() {
        ArrayList<Student> list = new ArrayList<>();
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://192.168.23.129:3306/db14", "root", "ljx19960802");
            st = con.createStatement(); //
            String sql = "SELECT * FROM student";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");
                Student stu = new Student(sid, name, age, birthday);
                list.add(stu);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            if(con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(st != null) {
                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }

        @Override
        public Student findById (Integer id){
            Student stu = null;
            Connection con = null;
            Statement st = null;
            ResultSet rs = null;
            try {
                Class.forName("com.mysql.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://192.168.23.129:3306/db14", "root", "ljx19960802");
                st = con.createStatement(); //
                String sql = "select * from student where sid="+id;
                rs = st.executeQuery(sql);
                while (rs.next()) {
                    Integer sid = rs.getInt("sid");
                    String name = rs.getString("name");
                    Integer age = rs.getInt("age");
                    Date birthday = rs.getDate("birthday");
                    stu = new Student(sid, name, age, birthday);
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                //6.释放资源
                if(con != null) {
                    try {
                        con.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }

                if(st != null) {
                    try {
                        st.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }

                if(rs != null) {
                    try {
                        rs.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
            return stu;
        }

        @Override
        public int insert (Student stu){
            Connection con = null;
            Statement st = null;
            int num = 0;
            try {
                Class.forName("com.mysql.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://192.168.23.129:3306/db14", "root", "ljx19960802");
                st = con.createStatement(); //
                String birthday = new SimpleDateFormat("yyyy-MM-dd").format(stu.getBirthday());
                String sql = "INSERT INTO student VALUES ('"+stu.getSid()+"','"+stu.getName()+"','"+stu.getAge()+"','"+birthday+"')";
                num = st.executeUpdate(sql);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                //6.释放资源
                if(con != null) {
                    try {
                        con.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }

                if(st != null) {
                    try {
                        st.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }

            }
            return num;
        }

        @Override
        public int update (Student stu){
            Connection con = null;
            Statement st = null;
            int num = 0;
            try {
                Class.forName("com.mysql.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://192.168.23.129:3306/db14", "root", "ljx19960802");
                st = con.createStatement(); //
                String birthday = new SimpleDateFormat("yyyy-MM-dd").format(stu.getBirthday());
                String sql = "UPDATE student SET sid='"+stu.getSid()+"',name='"+stu.getName()+"',age='"+stu.getAge()+"',birthday='"+birthday+"' WHERE sid='"+stu.getSid()+"'";
                num = st.executeUpdate(sql);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                //6.释放资源
                if(con != null) {
                    try {
                        con.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }

                if(st != null) {
                    try {
                        st.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }

            }
            return num;
        }

        @Override
        public int delete (Integer id){
            Connection con = null;
            Statement st = null;
            int num = 0;
            try {
                Class.forName("com.mysql.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://192.168.23.129:3306/db14", "root", "ljx19960802");
                st = con.createStatement(); //
                String sql = "DELETE FROM student WHERE sid='"+id+"'";
                num = st.executeUpdate(sql);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                //6.释放资源
                if(con != null) {
                    try {
                        con.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }

                if(st != null) {
                    try {
                        st.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }

            }
            return num;
        }
    }
